package com.sgcc.pda.data.util.db;

import android.content.ContentValues;
import android.content.Context;
import android.support.annotation.Nullable;
import android.text.TextUtils;
import android.util.Log;

import com.sgcc.pda.baselibrary.utils.LogUtil;
import com.sgcc.pda.greendao.dao.h5.DBH5Manager;

import net.sqlcipher.Cursor;
import net.sqlcipher.database.SQLiteDatabase;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * @author superFan
 * date:: 2019/7/17/017 10:49
 * company:: beijing_hezhongweiqi
 * description:: 提供给H5应用使用的数据库操作类：自主创建库、表
 */
public class DBH5Util {
    private static final String TAG = "DBH5Util";
    private static final String IS_SUCCESS = "isSuccess";
    private static final String INSERT_DATA_EMPTY="插入的数据集为空";

    private DBH5Util() {
    }

    private static class Instance {
        private static final DBH5Util DBH5UTIL = new DBH5Util();
    }

    public static DBH5Util getInstance() {
        return Instance.DBH5UTIL;
    }

    /**
     * 初始化工具
     *
     * @param context
     */
    public void init(Context context) {
        DBH5Manager.getInstance().init(context);
    }

    /**
     * 初始化数据库：数据库名，加密密码，版本号（暂无用）
     * 调用此方法并不会创建数据库，只有在第一次创建表的时候，才会创建数据库
     * 注意：SQLite不强制数据类型约束。任何数据都可以插入任何列。
     * 但SQLite确实使用声明的列类型来指示你所期望的格式（列亲和性）；
     * 即整形列插入字符串时，会试图将字符串转为整形，如果转不了，就插入字符串。
     *
     * @dbName 数据库名
     * @dbEncrypPwd 加密密钥
     * @version 版本号 （暂时无用，更新使用更新字段方法）
     */
    public String initDatabase(String dbName, String dbEncrypPwd, int version) {
        String reason = DBH5Manager.getInstance().initDatabase(dbName, dbEncrypPwd, version);
        if (reason.equals("true")) {
            return returnJson(true, "", "数据库初始化成功");
        } else {
            return returnJson(false, reason, null);
        }
    }


    /**
     * 创建表 如果是第一张表，会创建数据库
     *
     * @dbName 数据库名
     * @tableName 表名
     * @tableMap 表定义的字段《String，String》=《字段名，字段类型》
     * 字段类型可以是INTEGER、TEXT、NUMERIC、NULL、REAL、BLOB、VARCHAR(10)等
     * 也可以带其他限制条件如：INTEGER PRIMARY KEY AUTOINCREMENT、INTEGER default 0
     */
    public String create(String dbName, String tableName, Map<String, String> tableMap) {
        StringBuilder createTableSql = new StringBuilder();
        createTableSql.append("create table if not exists ")
                .append(tableName).append("(");
        Iterator<Map.Entry<String, String>> iterator = tableMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> next = iterator.next();
            createTableSql.append(next.getKey())
                    .append(" ")
                    .append(next.getValue());
            if (iterator.hasNext()) {
                createTableSql.append(",");
            }
        }
        createTableSql.append(")");
        try {
            Log.d(TAG, "H5创建表: " + createTableSql.toString());
            DBH5Manager.getInstance().getWriteDatabase(dbName)
                    .execSQL(createTableSql.toString());
            return returnJson(true, null, true);
        } catch (Exception e) {
            LogUtil.d(e.getLocalizedMessage());
            return returnJson(false, e.getLocalizedMessage(), null);
        }
    }

    /**
     * 创建表 如果是第一张表，会创建数据库 【未测试】
     *
     * @return
     * @dbName 数据库名
     * @tableName 表名
     * @tableMap 表定义的字段《String，String》=《字段名，字段类型》
     * 字段类型可以是INTEGER、TEXT、NUMERIC、NULL、REAL、BLOB、VARCHAR(10)等
     * 也可以带其他限制条件如：INTEGER PRIMARY KEY AUTOINCREMENT、INTEGER default 0
     */
    @Deprecated
    public String createWithDefault(String dbName, String tableName, Map<String, String> tableMap) {
        StringBuilder createTableSql = new StringBuilder();
        createTableSql.append("create table if not exists ")
                .append(tableName).append("(");
        Iterator<Map.Entry<String, String>> iterator = tableMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> next = iterator.next();
            createTableSql.append(next.getKey())
                    .append(" ")
                    .append(next.getValue());
            //通过判断null，来判断是否增加default 默认值
            String defaultData = getDefaultByColumnType(next.getValue());
            if (defaultData != null) {
                createTableSql.append(" default ").append(defaultData);
            }

            if (iterator.hasNext()) {
                createTableSql.append(",");
            }
        }
        createTableSql.append(")");
        try {
            Log.d(TAG, "H5创建表: " + createTableSql.toString());
            DBH5Manager.getInstance().getWriteDatabase(dbName)
                    .execSQL(createTableSql.toString());
            return returnJson(true, null, true);
        } catch (Exception e) {
            LogUtil.e(e.getLocalizedMessage());
            return returnJson(false, e.getLocalizedMessage(), null);
        }
    }

    /**
     * 自定义  根据列类型 获取的默认值
     *
     * @param columnType 数据表 列类型
     * @return
     */
    private String getDefaultByColumnType(String columnType) {
        //空类型、主键、自增，或已经含有默认值配置的 都不需要返回默认的列类型;
        //返回null 代表不进行默认值配置
        if (TextUtils.isEmpty(columnType) || columnType.toUpperCase().contains("KEY") ||
                columnType.toUpperCase().contains("AUTOINCREMENT") || columnType.toUpperCase().contains("DEFAULT")) {
            return null;
        }
        //整形INTEGER和浮点REAL，默认为0
        if (columnType.toUpperCase().contains("INTEGER") || columnType.toUpperCase().contains("NUMERIC")
                || columnType.toUpperCase().contains("REAL")) {
            return "0";
        }
        //字符串类型，默认为“”
        if (columnType.toUpperCase().contains("TEXT")) {
            return "";
        }
        //其他类型，默认为“”//此处可以添加更多类型的默认值
        return null;
    }

    /**
     * 插入一条数据
     *
     * @param dbName     数据库名
     * @param tableName  数据表名
     * @param privateKey 主键或唯一值名称
     * @param dataMap    插入的数据集《字段名，字段值》
     * @return
     */
    public String insertOne(String dbName, String tableName, String privateKey, Map<String, String> dataMap) {
        ContentValues values = new ContentValues();
        for (Map.Entry<String, String> entry : dataMap.entrySet()) {
            Log.d(TAG, "H5插入数据: " + entry.getKey() + ":" + entry.getValue());
            values.put(entry.getKey(), entry.getValue());
        }
        long isInsertSuccess = DBH5Manager.getInstance().getWriteDatabase(dbName).insert(tableName, null, values);
        if (isInsertSuccess == -1) {
            String query = query(dbName, tableName, null, dataMap.get(privateKey),
                    null, null, null, null);
            try {
                JSONObject jsonObject = new JSONObject(query);
                boolean isSuccess = jsonObject.getBoolean(IS_SUCCESS);
                if (isSuccess) {
                    isInsertSuccess = DBH5Manager.getInstance().getWriteDatabase(dbName).update(tableName, values,
                            privateKey + "=?", new String[]{dataMap.get(privateKey)});
                }
            } catch (JSONException e) {
                e.printStackTrace();
            }
        }
        return returnJson(!(isInsertSuccess == -1), "插入失败", "插入成功");
    }

    /**
     * 插入多条数据
     *
     * @param dbName
     * @param tableName
     * @param dataMapListStr 需要插入的数据集json: List<Map<String, String>> ;
     *                       map代表一条数据，map中的键值对代表对应的字段插入的数据
     * @return
     */
    public String insertList(String dbName, String tableName, String privateKey, String dataMapListStr) {
        boolean result = false;
        String reason = "";
        if (TextUtils.isEmpty(dataMapListStr)) {
            return returnJson(result, INSERT_DATA_EMPTY, null);
        }
        JSONArray jsonArray = null;
        try {
            jsonArray = new JSONArray(dataMapListStr);
            List<Map> list = new ArrayList<>();
            for (int i = 0; i < jsonArray.length(); i++) {
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                Iterator<String> keys = jsonObject.keys();
                Map<String, String> map = new HashMap<>();
                while (keys.hasNext()) {
                    String key = keys.next();
                    map.put(key, jsonObject.getString(key));
                }
                if (map != null && map.size() != 0) {
                    list.add(map);
                }
            }
            return insertList(dbName, tableName, privateKey, list);
        } catch (JSONException e) {
            reason = e.getLocalizedMessage();
            result = false;
        }
        return returnJson(result, reason, "插入成功");
    }

    /**
     * 插入多条数据
     *
     * @param dbName
     * @param tableName
     * @param dataMapList 需要插入的数据集 List<Map<String, String>> ;
     *                    map代表一条数据，map中的键值对代表对应的字段插入的数据
     * @return
     */
    public String insertList(String dbName, String tableName, String privateKey, List<Map> dataMapList) {
        boolean result = false;
        String reason = "";
        String msg = "插入成功";
        if (null == dataMapList || dataMapList.size() <= 0) {
            return returnJson(result, INSERT_DATA_EMPTY, null);
        }
        if (dataMapList != null && dataMapList.size() != 0) {
            try {
                beginTransaction(dbName);
                boolean isSuccess = true;
                for (Map<String, String> dataMap : dataMapList) {
                    JSONObject jsonObject = new JSONObject(insertOne(dbName, tableName, privateKey, dataMap));
                    isSuccess = jsonObject.getBoolean(IS_SUCCESS);
                    if (!isSuccess) {
                        reason = jsonObject.getString("reason");
                        break;
                    }
                }
                if (isSuccess) {
                    result = true;
                    setTransactionSuccessful(dbName);
                }
            } catch (Exception e) {
                e.printStackTrace();
                result = false;
                reason = e.getLocalizedMessage();
            } finally {
                endTransaction(dbName);
            }
        } else {
            reason = INSERT_DATA_EMPTY;
        }
        return returnJson(result, reason, msg);
    }

    /**
     * 删除表
     *
     * @param dbName    数据库名
     * @param tableName 表名
     * @return
     */
    public String dropTable(String dbName, String tableName) {
        try {
            JSONObject jsonObject = new JSONObject(isTableExist(dbName, tableName));
            boolean isExist = jsonObject.getBoolean(IS_SUCCESS);
            if (isExist) {
                DBH5Manager.getInstance().getWriteDatabase(dbName).execSQL("drop table " + tableName);
                Log.d(TAG, "删除数据表: " + tableName);
                return returnJson(true, null, "删除成功");
            } else {
                return returnJson(false, "表不存在", null);
            }
        } catch (JSONException e) {
            return returnJson(false, e.getLocalizedMessage(), null);
        }
    }

    /**
     * 清空数据表
     *
     * @param dbName    数据库名
     * @param tableName 表名
     * @return
     */
    public String clear(String dbName, String tableName) {
        try {
            JSONObject jsonObject = new JSONObject(isTableExist(dbName, tableName));
            boolean isExist = jsonObject.getBoolean(IS_SUCCESS);
            if (isExist) {
                DBH5Manager.getInstance().getWriteDatabase(dbName).execSQL("delete from " + tableName);
                Log.d(TAG, "清空数据表: " + tableName);
                return returnJson(true, null, "清空成功");
            } else {
                return returnJson(false, "表不存在", null);
            }
        } catch (JSONException e) {
            return returnJson(false, e.getLocalizedMessage(), null);
        }
    }

    /**
     * 更新数据[使用update]
     *
     * @param dbName         数据库名
     * @param tableName      表名
     * @param dataMap        更新的数据 《字段名，更新的值》
     * @param whereCondition 条件语句  不带where
     * @return
     */
    @Deprecated
    public boolean updateWithSQL(String dbName, String tableName, Map<String, String> dataMap, @Nullable String whereCondition) {
        if (dataMap != null && dataMap.size() != 0) {
            StringBuilder updateSql = new StringBuilder();
            updateSql.append("update ").append(tableName)
                    .append(" set ");
            Iterator<Map.Entry<String, String>> entriesIter = dataMap.entrySet().iterator();
            while (entriesIter.hasNext()) {
                Map.Entry<String, Object> entry = (Map.Entry) entriesIter.next();
                updateSql.append(entry.getKey());
                updateSql.append("=");
                updateSql.append("'" + entry.getValue() + "'");
                if (entriesIter.hasNext()) {
                    updateSql.append(", ");
                }
            }
            if (!TextUtils.isEmpty(whereCondition)) {
                updateSql.append(" where ").append(whereCondition);
            }
            try {
                Log.d(TAG, "H5更新数据: " + updateSql.toString());
                DBH5Manager.getInstance().getWriteDatabase(dbName)
                        .execSQL(updateSql.toString());
                return true;
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        } else {
            return false;
        }
    }

    /**
     * 更新数据
     *
     * @param dbName         数据库名
     * @param tableName      表名
     * @param dataMap        更新的数据 《字段名，更新的值》
     * @param whereCondition 条件语句  不带where
     * @return
     */
    public String update(String dbName, String tableName, Map<String, String> dataMap, @Nullable String whereCondition) {
        if (dataMap != null && dataMap.size() != 0) {
            ContentValues values = new ContentValues();
            for (Map.Entry<String, String> entry : dataMap.entrySet()) {
                Log.d(TAG, "H5更新数据: " + entry.getKey() + ":" + entry.getValue());
                values.put(entry.getKey(), entry.getValue());
            }
            try {
                int update = DBH5Manager.getInstance().getWriteDatabase(dbName)
                        .update(tableName, values, whereCondition, null);
                Log.d(TAG, "更新影响的条目数为: " + update);
                if (update > 0) {
                    return returnJson(true, null, "更新成功");
                } else {
                    return returnJson(false, null, "更新成功");
                }
            } catch (Exception e) {
                e.printStackTrace();
                return returnJson(false, e.getLocalizedMessage(), null);
            }
        } else {
            return returnJson(false, "更新的数据为空", null);
        }
    }

    /**
     * 查询数据库：
     *
     * @param dbName             数据库名
     * @param tableName          表名
     * @param selectColumnNames  查询列名
     * @param whereCondition     where条件语句
     * @param groupByColumnNames groupby 语句
     * @param haveingCondition   haveing语句
     * @param orderByCondition   orderby语句
     * @param limitCondition     limit语句
     * @return 数据库信息json； key为字段名
     */
    public String query(String dbName, String tableName, @Nullable List<String> selectColumnNames, @Nullable String whereCondition, List<String> groupByColumnNames, String haveingCondition, String orderByCondition, String limitCondition) {
        StringBuilder selectSql = new StringBuilder();
        //select
        selectSql.append("select ");
        if (selectColumnNames == null || selectColumnNames.size() == 0) {
            //查询所有列
            selectSql.append(" * ");
        } else {
            Iterator<String> iterator = selectColumnNames.iterator();
            while (iterator.hasNext()) {
                selectSql.append(iterator.next());
                if (iterator.hasNext()) {
                    selectSql.append(",");
                }
            }
        }
        selectSql.append(" from ").append(tableName);
        //where
        if (!TextUtils.isEmpty(whereCondition)) {
            selectSql.append(" where ").append(whereCondition);
        }
        //group by
        if (groupByColumnNames != null && groupByColumnNames.size() != 0) {
            selectSql.append(" group by ");
            Iterator<String> iterator = groupByColumnNames.iterator();
            while (iterator.hasNext()) {
                selectSql.append(iterator.next());
                if (iterator.hasNext()) {
                    selectSql.append(",");
                }
            }
        }
        //having
        if (!TextUtils.isEmpty(haveingCondition)) {
            selectSql.append(" having ").append(haveingCondition);
        }
        //order by
        if (!TextUtils.isEmpty(orderByCondition)) {
            selectSql.append(" order by ").append(orderByCondition);
        }
        //limit
        if (!TextUtils.isEmpty(limitCondition)) {
            selectSql.append(" limit ").append(limitCondition);
        }
        try (Cursor cursor = DBH5Manager.getInstance().getWriteDatabase(dbName).rawQuery(selectSql.toString(), null)) {
            if (cursor != null) {
                JSONArray resultJson = new JSONArray();
                while (cursor.moveToNext()) {
                    JSONObject itemJson = new JSONObject();
                    int columnCount = cursor.getColumnCount();
                    for (int i = 0; i < columnCount; i++) {
                        itemJson.put(cursor.getColumnName(i), cursor.getString(i));
                    }
                    resultJson.put(itemJson);
                }
                Log.d(TAG, "查询数据表 【" + tableName + "】:" + resultJson.toString() + "\n共" + resultJson.length() + "条数据");
                return returnJson(true, null, resultJson);
            }
            return returnJson(false, "没有符合查询条件的结果", null);
        } catch (Exception e) {
            e.printStackTrace();
            return returnJson(false, e.getLocalizedMessage(), null);
        }
    }

    /**
     * 联合查询数据库：
     *
     * @param dbName             数据库名
     * @param tableNames         表名(集合)
     * @param selectColumnNames  查询列名
     * @param whereConditions    where条件语句(若没有,传空字符串)
     * @param groupByColumnNames groupby 语句
     * @param haveingCondition   haveing语句
     * @param orderByCondition   orderby语句
     * @param limitCondition     limit语句
     * @return 数据库信息json； key为字段名
     */
    public String joinQuery(String dbName, List<String> tableNames, @Nullable List<String> selectColumnNames, @Nullable List<String> whereConditions,
                            List<String> groupByColumnNames, String haveingCondition, String orderByCondition, String limitCondition) {
//        select 列名... from 表名 join 表名 on 条件... join 表名 on 条件...(以此类推)
        JSONArray result = null;
        String reason = null;
        boolean isSuccess = false;
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("select ");
        if (selectColumnNames == null || selectColumnNames.size() == 0) {
            selectSql.append("* ");
        } else {
            Iterator<String> iterator = selectColumnNames.iterator();
            while (iterator.hasNext()) {
                selectSql.append(iterator.next());
                if (iterator.hasNext()) {
                    selectSql.append(",");
                }
            }
        }
        try {
            selectSql.append(" from ").append(tableNames.get(0));
            for (int i = 1; i < tableNames.size(); i++) {
                if (whereConditions == null || whereConditions.size() == 0) {
                    selectSql = joinTable(selectSql, tableNames.get(i), "");
                } else {
                    selectSql = joinTable(selectSql, tableNames.get(i), whereConditions.get(i - 1));
                }
            }
            //group by
            if (groupByColumnNames != null && groupByColumnNames.size() != 0) {
                selectSql.append(" group by ");
                Iterator<String> iterator = groupByColumnNames.iterator();
                while (iterator.hasNext()) {
                    selectSql.append(iterator.next());
                    if (iterator.hasNext()) {
                        selectSql.append(",");
                    }
                }
            }
            //having
            if (!TextUtils.isEmpty(haveingCondition)) {
                selectSql.append(" having ").append(haveingCondition);
            }
            //order by
            if (!TextUtils.isEmpty(orderByCondition)) {
                selectSql.append(" order by ").append(orderByCondition);
            }
            //limit
            if (!TextUtils.isEmpty(limitCondition)) {
                selectSql.append(" limit ").append(limitCondition);
            }
            Cursor cursor = DBH5Manager.getInstance().getWriteDatabase(dbName).rawQuery(selectSql.toString(), null);
            if (cursor != null) {
                isSuccess = true;
                result = new JSONArray();
                while (cursor.moveToNext()) {
                    JSONObject itemJson = new JSONObject();
                    int columnCount = cursor.getColumnCount();
                    for (int i = 0; i < columnCount; i++) {
                        itemJson.put(cursor.getColumnName(i), cursor.getString(i));
                    }
                    result.put(itemJson);
                }
                Log.d(TAG, "查询数据表:" + result.toString() + "\n共" + result.length() + "条数据");
            } else {
                reason = "Cursor为空";
            }
        } catch (NullPointerException e) {
            reason = e.getLocalizedMessage();
        } catch (IndexOutOfBoundsException e) {
            reason = e.getLocalizedMessage();
        } catch (JSONException e) {
            reason = e.getLocalizedMessage();
        }
        return returnJson(isSuccess, reason, result);
    }

    private StringBuilder joinTable(StringBuilder joinSql, String tableName, String whereCondition) {
        joinSql.append(" join ");
        joinSql.append(tableName);
        if (!TextUtils.isEmpty(whereCondition)) {
            joinSql.append(" on " + whereCondition);
        }
        return joinSql;
    }

    /**
     * 直接执行sql语句
     *
     * @param dbName
     * @param rawQuerySql
     * @return
     */
    public String rawQuerySql(String dbName, String rawQuerySql) {
        boolean isSuccess = false;
        String reason = null;
        JSONArray result = null;
        if (!TextUtils.isEmpty(rawQuerySql)) {
            try (Cursor cursor = DBH5Manager.getInstance().getWriteDatabase(dbName)
                    .rawQuery(rawQuerySql, null)) {
                if (cursor != null) {
                    isSuccess = true;
                    result = new JSONArray();
                    while (cursor.moveToNext()) {
                        JSONObject itemJson = new JSONObject();
                        int columnCount = cursor.getColumnCount();
                        for (int i = 0; i < columnCount; i++) {
                            itemJson.put(cursor.getColumnName(i), cursor.getString(i));
                        }
                        result.put(itemJson);
                    }
                    Log.d(TAG, "查询数据rqwQuery 【" + rawQuerySql + "】:" + result.toString() + "\n共" + result.length() + "条数据");
//                    return json;
                }
            } catch (Exception e) {
                reason = e.getLocalizedMessage();
            }
        }
        return returnJson(isSuccess, reason, result);
    }


    /**
     * 删除数据
     *
     * @param dbName         数据库名
     * @param tableName      表名
     * @param whereCondition where语句
     * @return
     */
    public String delete(String dbName, String tableName, String whereCondition) {
        try {
            int deleteRes = DBH5Manager.getInstance().getWriteDatabase(dbName)
                    .delete(tableName, whereCondition, null);
            Log.d(TAG, "删除数据: " + deleteRes + "条");
            if (deleteRes > 0) {
                return returnJson(true, null, "删除成功");
            } else {
                return returnJson(false, "数据不存在", null);
            }
        } catch (Exception e) {
            return returnJson(false, e.getLocalizedMessage(), null);
        }
    }

    /**
     * 直接执行sql语句
     *
     * @param dbName
     * @param sqlStr
     * @return
     */
    public String rawExecSQL(String dbName, String sqlStr) {
        if (!TextUtils.isEmpty(sqlStr)) {
            try {
                DBH5Manager.getInstance().getWriteDatabase(dbName).rawExecSQL(sqlStr);
                return returnJson(true, null, "执行成功");
            } catch (Exception e) {
                return returnJson(false, e.getLocalizedMessage(), null);
            }
        } else {
            return returnJson(false, "sql语句为空", null);
        }
    }

    /**
     * 获取数据库信息
     *
     * @param dbName 数据库名
     * @return 数据库信息json：version=版本号；path=路径; tableList=数据库表名(含有系统表sqlite_master)
     */
    public String getDBInfo(String dbName) {
        String reason = null;
        boolean isSuccess = false;
        JSONObject dbInfo = new JSONObject();
        SQLiteDatabase writeDatabase = DBH5Manager.getInstance().getWriteDatabase(dbName);
        if (writeDatabase != null) {
            //sqlite_master 是系统表,tbl_name,name 是系统表中存储的表名字段,【不能用大写】WHERE TYPE='table' ORDER BY tbl_name
            try (Cursor cursor = writeDatabase
                    .rawQuery("select name from sqlite_master", null)) {
                isSuccess = true;
                StringBuilder tableSb = new StringBuilder();
                while (cursor.moveToNext()) {
                    //遍历出表名
                    String name = cursor.getString(0);
                    tableSb.append(name);
                    if (!cursor.isLast()) {
                        tableSb.append(",");
                    }
                }
                dbInfo.put("tableList", tableSb.toString());
                dbInfo.put("version", writeDatabase.getVersion());
                dbInfo.put("path", writeDatabase.getPath());
                String path = dbInfo.getString("path");
                System.out.println(path);
            } catch (JSONException e) {
                return returnJson(false, e.getLocalizedMessage(), null);
            }
        }
        return returnJson(isSuccess, reason, dbInfo);
    }

    /**
     * 获取表结构信息
     *
     * @param dbName    数据库名
     * @param tableName 表名
     * @return
     */
    public String getTableStructureInfo(String dbName, String tableName) {
        String reason = null;
        boolean isSuccess = false;
        StringBuilder tableNamesSb = new StringBuilder();
        SQLiteDatabase writeDatabase = DBH5Manager.getInstance().getWriteDatabase(dbName);
        try (Cursor cursor = writeDatabase.rawQuery("pragma table_info(" + tableName + ")", null)) {
            isSuccess = true;
            while (cursor.moveToNext()) {
                tableNamesSb.append(cursor.getString(1));//字段名
                tableNamesSb.append(":");
                tableNamesSb.append(cursor.getString(2));//字段类型
                if (!cursor.isLast()) {
                    tableNamesSb.append(",");
                }
            }
        } catch (Exception e) {
            reason = e.getLocalizedMessage();
        }
        return returnJson(isSuccess, reason, tableNamesSb.toString());
    }


    /**
     * 判断数据库中的 表是否存在
     *
     * @param dbName    数据库
     * @param tableName 表名
     * @return
     */
    public String isTableExist(String dbName, String tableName) {
        boolean isSuccess = false;
        boolean isExist = false;
        String reason = "";
        if (TextUtils.isEmpty(tableName)) {
            return returnJson(isSuccess, "表名为空", null);
        }
        try (Cursor cursor = DBH5Manager.getInstance().getWriteDatabase(dbName).rawQuery("pragma table_info(" + tableName + ")", null)) {
            isSuccess = true;
            if (cursor.getCount() > 0) {
                isExist = true;
            }
            if (!isExist) {
                reason = "表不存在";
            }
        } catch (Exception e) {
            reason = e.getLocalizedMessage();
        }
        return returnJson(isSuccess, reason, isExist);
    }

    /**
     * 修改表名
     *
     * @param dbName
     * @param oldTableName 旧表表名
     * @param newTableName 新表表名
     * @return
     */
    public String reNameTable(String dbName, String oldTableName, String newTableName) {
        boolean isSuccess = false;
        String reason = null;
        String result = "表名修改成功";
        if (TextUtils.isEmpty(oldTableName) || TextUtils.isEmpty(newTableName)) {
            return returnJson(isSuccess, "表名为空", null);
        }
        try {
            DBH5Manager.getInstance().getWriteDatabase(dbName).rawExecSQL("alter table " + oldTableName + " rename to " + newTableName);
            isSuccess = true;
        } catch (Exception e) {
            e.printStackTrace();
            reason = e.getLocalizedMessage();
        }
        return returnJson(isSuccess, reason, result);
    }

    /**
     * 添加 表 字段
     *
     * @param dbName
     * @param tableName    表名
     * @param addColumnMap 字段map  字段名 字段类型
     * @return
     */
    public String addColumn(String dbName, String tableName, Map<String, String> addColumnMap) {
        boolean isSuccess = false;
        String reason = null;
        String result = "字段添加成功";
        if (TextUtils.isEmpty(tableName)) {
            return returnJson(isSuccess, "表名为空", null);
        }
        if (addColumnMap != null && addColumnMap.size() != 0) {
            StringBuilder addTableSb = new StringBuilder();
            try {
                addTableSb.append("alter table ").append(tableName).append(" add ");
                Iterator<Map.Entry<String, String>> iterator = addColumnMap.entrySet().iterator();
                while (iterator.hasNext()) {
                    Map.Entry entry = iterator.next();
                    addTableSb.append(entry.getKey()).append(" ").append(entry.getValue());
                    if (iterator.hasNext()) {
                        addTableSb.append(",");
                    }
                }
                DBH5Manager.getInstance().getWriteDatabase(dbName)
                        .rawExecSQL(addTableSb.toString());
                isSuccess = true;
            } catch (Exception e) {
                e.printStackTrace();
                reason = e.getLocalizedMessage();
            }
        }
        return returnJson(isSuccess, reason, result);
    }

    /**
     * 开启事务 推荐transaction
     *
     * @param dbName
     */
    public void beginTransaction(String dbName) {
        DBH5Manager.getInstance().getWriteDatabase(dbName).beginTransaction();
    }

    /**
     * 标记事务执行成功 推荐transaction
     *
     * @param dbName
     */
    public void setTransactionSuccessful(String dbName) {
        DBH5Manager.getInstance().getWriteDatabase(dbName).setTransactionSuccessful();
    }

    /**
     * 结束事务 推荐transaction
     *
     * @param dbName
     */
    public void endTransaction(String dbName) {
        DBH5Manager.getInstance().getWriteDatabase(dbName).endTransaction();
    }

    /**
     * 执行事务
     *
     * @param dbName
     * @param sqlArray sql语句列表
     * @return
     */
    public String transaction(String dbName, List<String> sqlArray) {
        SQLiteDatabase writeDatabase = DBH5Manager.getInstance().getWriteDatabase(dbName);
        boolean isSuccess = true;
        String reason = null;
        String result = "事务执行成功";
        if (writeDatabase != null) {
            writeDatabase.beginTransaction();
            try {
                for (String sql : sqlArray) {
                    writeDatabase.rawExecSQL(sql);
                }
                writeDatabase.setTransactionSuccessful();
            } catch (Exception e) {
                reason = e.getLocalizedMessage();
                isSuccess = false;
            } finally {
                try {
                    if (null != writeDatabase) {
                        writeDatabase.endTransaction();
                        writeDatabase.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } else {
            isSuccess = false;
        }
        return returnJson(isSuccess, reason, result);
    }

    /**
     * 关闭指定数据库：数据库名
     * dbName 数据库名
     */
    public String closeDatabase(String dbName) {
        String reason = DBH5Manager.getInstance().closeSqliteHelper(dbName);
        if (reason.equals("true")) {
            returnJson(true, null, dbName + "关闭成功");
        }
        return returnJson(false, reason, null);
    }

    /**
     * 关闭所有数据库
     */
    public String closeAllSqliteHelper() {
        String reason = DBH5Manager.getInstance().closeAllSqliteHelper();
        if (reason.equals("true")) {
            returnJson(true, null, "数据库关闭成功");
        }
        return returnJson(false, reason, null);
    }

    private String returnJson(boolean isSuccess, String reason, Object result) {
        JSONObject returnResult = new JSONObject();
        try {
            returnResult.put(IS_SUCCESS, isSuccess);
            returnResult.put("reason", reason);
            returnResult.put("result", result);
        } catch (JSONException e) {
            e.printStackTrace();
        }
        return returnResult.toString();
    }
}
